CREATE PROCEDURE [dbo].[asi_GroupCopyDetailMembership] (@srcGroupKey uniqueidentifier, @destGroupKey uniqueidentifier, @userKey uniqueidentifier, @now datetime)
AS
BEGIN

-- Create temporary table to hold the results of a join of GroupMember and GroupMemberDetail
CREATE TABLE #tmpTable (GroupMemberKey uniqueidentifier, GroupKey uniqueidentifier, MemberContactKey uniqueidentifier, MemberIsActive bit, CreatedByUserKey uniqueidentifier, CreatedOn datetime,
                UpdatedByUserKey uniqueidentifier, UpdatedOn datetime, DropDate datetime, JoinDate datetime,
                GroupRoleKey uniqueidentifier, EffectiveDate datetime, ExpirationDate datetime, TypeSpecificData nvarchar(250), TypeSpecificKey uniqueidentifier, TypeSpecificFlag bit, TypeSpecificInt int,
                DetailGroupMemberStatusCode nvarchar(4), DetailIsActive bit, GroupMemberDetailKey uniqueidentifier)

-- Fill the temporary table with the results of the join, creating new ids for the GroupMemberKey and GroupMemberDetailKey, and setting other columns to specified values
INSERT INTO #tmpTable (GroupMemberKey, GroupKey, MemberContactKey, MemberIsActive, CreatedByUserKey, CreatedOn, UpdatedByUserKey,UpdatedOn,
                DropDate, JoinDate,
                GroupRoleKey, EffectiveDate, ExpirationDate, TypeSpecificData, TypeSpecificKey, TypeSpecificFlag, TypeSpecificInt,
                DetailGroupMemberStatusCode, DetailIsActive, GroupMemberDetailKey)
    SELECT newid(), @destGroupKey, gm.MemberContactKey, gm.IsActive, @userKey, @now, @userKey, @now, gm.DropDate, gm.JoinDate,
            gmd.GroupRoleKey, gmd.EffectiveDate, gmd.ExpirationDate, gmd.TypeSpecificData, gmd.TypeSpecificKey, gmd.TypeSpecificFlag, gmd.TypeSpecificInt,
            gmd.GroupMemberStatusCode, gmd.IsActive, newid()
    FROM GroupMember gm INNER JOIN GroupMemberDetail gmd ON gm.GroupMemberKey = gmd.GroupMemberKey
    WHERE gm.GroupKey = @srcGroupKey

-- Fill the GroupMember table from the temporary table, selecting distinct GroupMemberKey rows
INSERT INTO GroupMember (GroupMemberKey, GroupKey, MemberContactKey, IsActive, CreatedByUserKey, CreatedOn, UpdatedByUserKey, UpdatedOn, DropDate, JoinDate)
    SELECT DISTINCT GroupMemberKey, GroupKey, MemberContactKey, MemberIsActive, CreatedByUserKey, CreatedOn, UpdatedByUserKey, UpdatedOn, DropDate, JoinDate
        FROM #tmpTable

-- Fill the GroupMemberDetail table from the temporary table, using all the rows
INSERT INTO GroupMemberDetail (GroupMemberKey, GroupRoleKey, EffectiveDate, ExpirationDate, TypeSpecificData, TypeSpecificKey, TypeSpecificFlag, CreatedOn, CreatedByUserKey, UpdatedOn, UpdatedByUserKey,
                    TypeSpecificInt, GroupMemberStatusCode, IsActive, GroupMemberDetailKey, GroupKey)
    SELECT GroupMemberKey, GroupRoleKey, EffectiveDate, ExpirationDate, TypeSpecificData, TypeSpecificKey, TypeSpecificFlag, CreatedOn, CreatedByUserKey, UpdatedOn, UpdatedByUserKey,
            TypeSpecificInt, DetailGroupMemberStatusCode, DetailIsActive, GroupMemberDetailKey, GroupKey
        FROM #tmpTable

DROP TABLE #tmpTable

END

GO